/*** THIS PRODUCES THE SCHOOL DATA AND IS BASED ON REGISTERDATA for the paper
Individual Agency in Street-Level Bureaucrats’ Implementation of Policy Reforms: 
The Role of their Policy Evaluation and Self-Efficacy ***/

cd "datapathwheredatahasbeenplaced\"


/************* THIS CREATES THE PARENTAL BACKGROUND CHARACTERISTICS FOR STUDENTS ****/
forvalues v = 2008(1)2017 {
use "populationdata.dta" ,clear
foreach i of varlist * {
local j =lower("`i'")
capture: rename `i' `j'
}

capture keep pnr mor_id far_id civst koen ie_type hfaudd antboernf foed_dag
gen year = `v'+1
rename mor_id pnrm
rename far_id pnrf
gen male=(koen==1)
bys pnr: keep if pnr!=pnr[_n-1]

gen foedaar=year(foed_dag)
gen alder=year-foedaar


save pop`v'.dta, replace
}

use pop2008
forvalues v = 2009(1)2017 {
append using pop`v'
}
save popo.dta, replace

foreach c in m f {
forvalues v=2008(1)2016{
di `v'
use pnr`c' using popo.dta, clear
bys pnr`c': keep if pnr`c' !=pnr`c'[_n-1]
rename pnr`c' pnr
bys pnr: keep if pnr!=pnr[_n-1]
merge 1:m pnr using "populationdata`v'.dta"
foreach i of varlist * {
local j =lower("`i'")
capture: rename `i' `j'
}
 cap rename dispon_13 dispon_ny`c'
capture rename socio13 socio02

cap rename ansdage ansdage`c'
cap rename plads plads`c'
cap rename pstill pstill`c'
cap rename socstil_kode socstil_kode`c' 

drop _merge
cap keep pnr kom dispon_ny`c' socio02 hfaudd familie_type ansdage`c' plads`c' ///
pstill`c' socstil_kode`c' 



gen year=`v'+1

 tostring hfaudd, replace
 joinby hfaudd using udd.dta, unmatched(both)
 tab _m
 drop if _m==2
 drop _m
 tab h1
 tab H1TEKST, mis
 gen udd`c'=1 if inlist(h1,"10","15")
 replace udd`c'=2 if inlist(h1,"20","25")
 replace udd`c'=3 if inlist(h1,"35","39")
replace udd`c'=4 if inlist(h1,"40","50")
replace udd`c'=5 if inlist(h1,"60","65","70")

gen out`c'=(socio02>139 | socio02==.)
rename hfaudd hfaudd`c'
rename (kom hfaudd`c') (kom_`c' audd`c')
rename h1 h1`c'

destring kom_`c', replace

/**** DISPOSABLE INCOME**/
gen IHSdispon`c' =log(dispon_ny`c'+sqrt(dispon_ny`c'^2+1))
cap gen eneforaeldre`c'=(familie_type=="05" | familie_type=="09")
cap gen eneforaeldre`c'=(familie_type==05 | familie_type==09)



xtile indkq`c'= IHSdispon`c', nq(4)
qui tab indkq`c', gen(indkq`c'_)  

rename pnr pnr`c'
drop familie_type
save pop`v'.dta,replace
}
use pop2008.dta, clear
forvalues v = 2009(1)2016 {
append using pop`v'
}
compress
save foraeldre`c'.dta, replace
for Z in num 2008/2016: erase popZ.dta
}

use "FTDB2012.dta", clear
rename pnr pnrm
drop if pnrm==""
gen x=(pnrm!="")
egen sibs=sum(x), by(pnrm)
keep pnrm sibs
save sibs.dta, replace


use popo.dta, clear
joinby pnrm year using foraeldrem.dta, unmatched(master)
tab _m
drop _m
joinby pnrm using sibs.dta, unmatched(master)
tab _m
drop _m
joinby pnrf year using foraeldref.dta, unmatched(master)
tab _m
drop _m


bys pnr (year): replace ie_type=ie_type[_n-1] if ie_type==.

sort pnr year sibs


gen famindk=IHSdisponm if !mi(IHSdisponm)
replace famindk=IHSdisponf if mi(famindk)
replace famindk=IHSdisponm+IHSdisponf if !mi(IHSdisponf) & !mi(IHSdisponm)
xtile famindk_q= famindk, nq(4)
qui tab famindk_q, gen(famindk_)

gen hoejudd=uddf if uddf>uddm & !mi(uddf)
replace hoejudd=uddm if uddm>uddf & !mi(uddm)
replace hoejudd=uddm if uddm==uddf
replace hoejudd=uddm if uddf==.
replace hoejudd=uddf if uddm==.

bys pnr year: gen raus=_n
drop if raus==2
drop raus


egen tag =tag(pnr year)
tab tag
drop if tag==0

save foraeldrepanel.dta, replace

/*** WE USE THE STUDENT-SURVEYS TO CONSTRUCT THE STUDENT-FILES AND MERGE THEM TO THE SCHOOLS ***/
use elever_2014.dta, clear
keep pnr aar instnr skoleaar
append using elever_2015.dta
keep pnr aar instnr skoleaar
append using elever_2016.dta
keep pnr aar instnr skoleaar
append using elever_2017.dta
keep pnr aar instnr skoleaar
append using elever_2018.dta, clear
keep pnr aar instnr skoleaar


rename aar year


merge m:1 pnr year using foraeldrepanel.dta
keep if _merge==3

/***** PARENTS EDUCATIONAL LEVELS ***/
destring h1f h1m, replace
bys pnr (year): replace h1f=h1f[_n-1] if h1f==.
bys pnr (year): replace h1m=h1m[_n-1] if h1m==.


gen grundf=(h1f<20 | h1f>70)
gen ungerhvevf=(h1f>20 & h1f<40)
gen unif=(h1f>=40)

gen grundm=(h1m<20 | h1m>70)
gen ungerhvevm=(h1m>20 & h1m<40)
gen unim=(h1m>=40)

gen forudd=grundm 
replace forudd=ungerhvevm if grundm==0 & unim==0
replace  forudd=unim if grundm==0 & ungerhvevm==0


/**** STUDENT ETHNICITY ***/
gen andenetnisk=1
replace andenetnisk=0 if ie_type==1


/*** PARENTAL EDUCATIONAL LEVELS ***/
recode h1f (10 = 1 "Primary") (20 25 = 2 "Upper Secondary") (30 35= 3 "Lower Secondary") ///
(40=4 "Lower Tertiary") (50=5 "Intermediate Tertiary") ///
(60 65 70=6 "Upper Tertiary"), gen(far_udd)

recode h1m (10 = 1 "Primary") (20 25 = 2 "Upper Secondary") (30 35= 3 "Lower Secondary") ///
(40=4 "Lower Tertiary") (50=5 "Intermediate Tertiary") ///
(60 65 70=6 "Upper Tertiary"), gen(mor_udd)

tab mor_udd, gen (morudd_)
tab far_udd, gen (farudd_)

xtile indkqf= IHSdisponf, nq(15)
xtile indkqm= IHSdisponm, nq(15)


/******* CREATING A MEASURE OF SES ***/
factor  far_udd mor_udd indkqf indkqm  if ideel==1 | ideel==4 , factor(1)
predict ses, reg

su ses, det
gen sesp1=r(p1)
gen sesp5=r(p5)
gen sesp10=r(p10)
gen sesp25=r(p25)
gen sesp50=r(p50)
gen sesp75=r(p75)
gen sesp90=r(p90)
gen sesp95=r(p95)
gen sesp99=r(p99)
gen ses_kat=.
replace ses_kat=1 if ses <=sesp1
replace ses_kat=2 if ses >sesp1 & ses <=sesp5
replace ses_kat=3 if ses >sesp5 & ses <=sesp10
replace ses_kat=4 if ses >sesp10 & ses <=sesp25
replace ses_kat=5 if ses >sesp25 & ses <=sesp50
replace ses_kat=6 if ses >sesp50 & ses <=sesp75
replace ses_kat=7 if ses >sesp75 & ses <=sesp90
replace ses_kat=8 if ses >sesp90 & ses <=sesp95
replace ses_kat=9 if ses >sesp95 & ses <=sesp99
replace ses_kat=10 if ses >sesp99 & !mi(ses)

label define ses_kat 1 "1%" 2 "5%" 3 "10%" 4 "25%" 5 "50%" 6 "75%" 7 "90%" 8 "95%" 9 "99%" 10 "over 99%"
label values ses_kat ses_kat

tab ses_kat, gen(ses_kat)

/********** AGGREGATE ON SCHOOL-LEVEL ****/
foreach var of varlist antboernf andenetnisk ses_kat1 ses_kat2 ses_kat3 ses_kat4 ses_kat6 ses_kat7 ses_kat8 ses_kat9 ses_kat10  {
bys instnr year: egen total`var' =mean (`var')
}

bys instnr year: gen antalelever=_N

keep instnr year total* antalevelever


save samletelskl.dta, replace

